<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/1/19
 * Time: 15:32
 */

namespace app\controllers;

use app\lib\BaseFunction;
use app\models\Bank_bill;
use app\models\Baoxiao;
use app\models\ContainerModel;
use app\models\ContainerProductModel;
use app\models\Customer;
use app\models\CustomerRefer;
use app\models\Income;
use app\models\Invoice_new;
use app\models\Product;
use app\models\Purchase;
use app\models\Staff;
use app\service\IncomeService;
use app\service\OrderService;
use app\service\TableService;
use yii;

class DownloadController extends ApiBaseController
{
    /**
     * 导出报销excel
     */
    public function actionReimburse()
    {
        $where               = [];
        $where['job_number'] = yii::$app->request->get('job_number');
        $where['status']     = yii::$app->request->get('status');
        $where['type']       = yii::$app->request->get('type');
        $where['type2']      = yii::$app->request->get('type2');
        $where['invoice']    = trim(yii::$app->request->get('invoice'));
        $where['begin_date'] = yii::$app->request->get('begin_date');
        $where['end_date']   = yii::$app->request->get('end_date');
        $staffModel          = new Staff();
        $staffList           = $staffModel->get_all_saleman();
        $baoxiaoModel        = new Baoxiao();
        $list                = $baoxiaoModel->get_baoxiao_list(1, 1, $where);
        $excel               = new \PHPExcel();
        try {
            $excel->getActiveSheet()->setCellValue('A1', '登记日期');
            $excel->getActiveSheet()->setCellValue('B1', '报销时间');
            $excel->getActiveSheet()->setCellValue('C1', '报销申请人');
            $excel->getActiveSheet()->setCellValue('D1', '报销金额');
            $excel->getActiveSheet()->setCellValue('E1', '报销名称');
            $excel->getActiveSheet()->setCellValue('F1', '报销数量');
            $excel->getActiveSheet()->setCellValue('G1', 'invoice');
            $excel->getActiveSheet()->setCellValue('H1', '客户编号');
            $excel->getActiveSheet()->setCellValue('I1', '客户全名');
            $excel->getActiveSheet()->setCellValue('J1', '性质');
            $excel->getActiveSheet()->setCellValue('K1', '类型');
            $excel->getActiveSheet()->setCellValue('L1', '备注');
            foreach ($list as $k => $v) {
                $info = json_decode($v['detail'], true);
                $num  = $k + 2;
                $excel->getActiveSheet()->setCellValue('A' . $num, $info['date']);
                $excel->getActiveSheet()->setCellValue('B' . $num, date('Y-m-d', $v['time']));
                foreach ($staffList as $v2) {
                    if ($v['job_number'] == $v2['job_number']) {
                        $excel->getActiveSheet()->setCellValue('C' . $num, $v2['name']);
                        break;
                    }
                }
                $excel->getActiveSheet()->setCellValue('D' . $num, $info['fee']);
                $excel->getActiveSheet()->setCellValue('E' . $num, $info['name']);
                $excel->getActiveSheet()->setCellValue('F' . $num, $info['number']);
                $excel->getActiveSheet()->setCellValue('G' . $num, $v['invoice']);
                $excel->getActiveSheet()->setCellValue('H' . $num, $v['customer_name']);
                $excel->getActiveSheet()->setCellValue('I' . $num, $v['customer_num']);
                $excel->getActiveSheet()->setCellValue('J' . $num, $v['type'] > 0 ? yii::$app->params['reimburse_type'][$v['type']] : '');
                $excel->getActiveSheet()->setCellValue('K' . $num, $v['type2'] > 0 ? yii::$app->params['reimburse_type2'][$v['type2']] : '');
                $excel->getActiveSheet()->setCellValue('L' . $num, $info['remark']);
            }
            $name = '报销';
            header('Content-Type: application/vnd.ms-excel');
            header('Content-Disposition: attachment;filename="' . $name . '.xls"');
            header('Cache-Control: max-age=0');
            $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
            $objWriter->save('php://output');
        } catch (\Exception $e) {
            echo $e->getMessage();
        }

    }

    /**导出收款excel
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function actionDownloadIncome()
    {
        $where['pay_from']      = is_numeric(yii::$app->request->get('pay_type')) ? yii::$app->request->get('pay_type') : 0;
        $where['begin_time']    = yii::$app->request->get('begin_time');
        $where['end_time']      = yii::$app->request->get('end_time');
        $where['service_name']  = trim(yii::$app->request->get('service_name'));
        $where['number']        = trim(yii::$app->request->get('number'));
        $where['customer_name'] = trim(yii::$app->request->get('customer_name'));
        $where['invocie_num']   = trim(yii::$app->request->get('invocie'));
        $where['property_p']    = yii::$app->request->get('property_p');
        $service                = new IncomeService();
        $service->list($where);
    }

    /**订单汇总下载
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function actionDownloadOrder()
    {
        $where['job_number']         = yii::$app->request->get('job_number');
        $where['customer_name']      = yii::$app->request->get('customer_name');
        $where['invoice']            = yii::$app->request->get('invoice');
        $where['begin_date']         = yii::$app->request->get('begin_date');
        $where['end_date']           = yii::$app->request->get('end_date');
        $where['confirm_begin_date'] = yii::$app->request->get('confirm_begin_date');
        $where['confirm_end_date']   = yii::$app->request->get('confirm_end_date');
        $where['invoice_status']     = yii::$app->request->get('invoice_status');
        $where['order_status']       = yii::$app->request->get('order_status');
        $where['purchase']           = yii::$app->request->get('purchase');
        $where['recommend']          = yii::$app->request->get('recommend');
        $where['is_jiesuan']         = yii::$app->request->get('is_jiesuan');
        //业务员查看
        $currentUser = $this->currentUser();
        if ($currentUser['role'] == 3) {
            $where['job_number'] = $this->user['job_number'];
        } elseif ($currentUser['role'] == 4) {
            $where['purchase'] = $this->user['job_number'];
        }
        $model             = new Staff();
        $staffList         = $model->get_all_saleman();
        $service           = new TableService();
        $customerRefer     = new CustomerRefer();
        $customerReferList = $customerRefer->list();
        $list              = $service->mergeOrderData($where);
        $invoice_status    = require ROOT_PATH . '/config/invoice_status.php';
        $order_status      = yii::$app->params['order_status'];
        $excel             = new \PHPExcel();
        $excel->setActiveSheetIndex(0)
            ->setCellValue('A1', '日期')
            ->setCellValue('B1', 'invoice')
            ->setCellValue('C1', '客户编号')
            ->setCellValue('D1', '客户名称')
            ->setCellValue('E1', '推荐人')
            ->setCellValue('F1', '业务员')
            ->setCellValue('G1', '采购')
            ->setCellValue('H1', '产品')
            ->setCellValue('I1', 'invoice总金额')
            ->setCellValue('J1', '收入')
            ->setCellValue('K1', '支出')
            ->setCellValue('L1', '费用')
            ->setCellValue('M1', '净额')
            ->setCellValue('N1', '差额')
            ->setCellValue('O1', '备注')
            ->setCellValue('P1', '运费')
            ->setCellValue('Q1', '打印费')
            ->setCellValue('R1', '仓库费')
            ->setCellValue('S1', '大货')
            ->setCellValue('T1', '其他')
            ->setCellValue('U1', '内陆费')
            ->setCellValue('V1', '报销费')
            ->setCellValue('W1', '美元汇率')
            ->setCellValue('X1', '利润')
            ->setCellValue('Y1', '利润率')
            ->setCellValue('Z1', '订单状态')
            ->setCellValue('AA1', '订单属性')
            ->setCellValue('AB1', '业务员提成')
            ->setCellValue('AC1', '采购提成')
            ->setCellValue('AD1', '是否结算');
        $jiesuanStatus = [1 => '未结算', 2 => '财务审核', 3 => '已结算'];
        foreach ($list as $k => $v) {

            $num = $k + 2;
            $excel->setActiveSheetIndex(0)->setCellValue('AD' . $num, $jiesuanStatus[$v['is_jiesuan']]);
            $excel->setActiveSheetIndex(0)->setCellValue('A' . $num, $v['date'])
                ->setCellValue('B' . $num, $v['invoice']);
            $excel->setActiveSheetIndex(0)->setCellValue('C' . $num, $v['customer_num']);
            $excel->setActiveSheetIndex(0)->setCellValue('D' . $num, $v['customer_name']);
            foreach ($customerReferList as $v2) {
                if ($v['recommend'] == $v2['id']) {
                    $excel->setActiveSheetIndex(0)->setCellValue('E' . $num, $v2['name']);
                    break;
                }
            }
            foreach ($staffList as $v2) {
                if ($v['saleman_num'] == $v2['job_number']) {
                    $excel->setActiveSheetIndex(0)->setCellValue('F' . $num, $v2['name']);
                    break;
                }
            }
            foreach ($staffList as $v2) {
                if ($v['purchase'] == $v2['job_number']) {
                    $excel->setActiveSheetIndex(0)->setCellValue('G' . $num, $v2['name']);
                    break;
                }
            }
            $excel->setActiveSheetIndex(0)->setCellValue('H' . $num, $v['product'])
                ->setCellValue('I' . $num, $v['order_pay']);
            $tmp = 0;
            if (isset($v['income'])) {
                $tmp = $v['income']['in_come'] + $v['income']['total_fee'] + $v['income']['fee'];
                $excel->setActiveSheetIndex(0)
                    ->setCellValue('J' . $num, $v['income']['in_come'])
                    ->setCellValue('K' . $num, $v['income']['total_fee'])
                    ->setCellValue('L' . $num, $v['income']['fee'])
                    ->setCellValue('M' . $num, $tmp)
                    ->setCellValue('N' . $num, $tmp - $v['order_pay']);
            }
            $excel->setActiveSheetIndex(0)->setCellValue('O' . $num, $v['remark']);
            $express = 0;
            if (isset($v['express_fee'])) {
                $express = $v['express_fee'];
                $excel->setActiveSheetIndex(0)->setCellValue('P' . $num, $v['express_fee']);
            }
            $print = 0;
            if (isset($v['print_fee'])) {
                $print = $v['print_fee'];
                $excel->setActiveSheetIndex(0)->setCellValue('Q' . $num, $v['print_fee']);
            }
            $repo = 0;
            if (isset($v['repo_fee'])) {
                $repo = $v['repo_fee'];
                $excel->setActiveSheetIndex(0)->setCellValue('R' . $num, $v['repo_fee']);
            }
            $bank = 0;
            if (isset($v['bank'])) {
                $bank = $v['bank'];
                $excel->setActiveSheetIndex(0)->setCellValue('S' . $num, $v['bank']);
            }
            $others = 0;
            if (isset($v['others'])) {
                $others = $v['others'];
                $excel->setActiveSheetIndex(0)->setCellValue('T' . $num, $v['others']);
            }
            $inland = 0;
            if (isset($v['inland_fee'])) {
                $inland = $v['inland_fee'];
                $excel->setActiveSheetIndex(0)->setCellValue('U' . $num, $v['inland_fee']);
            }
            $baoxiao = 0;
            if (isset($v['baoxiao'])) {
                $baoxiao = $v['baoxiao'];
                $excel->setActiveSheetIndex(0)->setCellValue('V' . $num, $baoxiao);
            }

            $exchange = $v['exchange_rate'] ?? 0;
            $excel->setActiveSheetIndex(0)->setCellValue('W' . $num, $exchange);
            $profit = $tmp * $exchange - $express - $print - $repo + $bank + $others - $inland - $baoxiao;
            $excel->setActiveSheetIndex(0)->setCellValue('X' . $num, round($profit, 2));
            //添加提成
            $re = BaseFunction::profit($v, $profit, $staffList);
            $excel->setActiveSheetIndex(0)->setCellValue('AB' . $num, $re[0]);
            $excel->setActiveSheetIndex(0)->setCellValue('AC' . $num, $re[1]);
            $rate = ($tmp * $exchange > 0) ? (round(($profit / ($tmp * $exchange) * 100), 2) . '%') : 0;
            $excel->setActiveSheetIndex(0)->setCellValue('Y' . $num, $rate);
            $invoice_status[0] = '未成单，已退回';
            $excel->setActiveSheetIndex(0)->setCellValue('Z' . $num, $invoice_status[$v['invoice_status']]);
            $excel->setActiveSheetIndex(0)->setCellValue('AA' . $num, $order_status[$v['order_status']]);

        }
        $name = '订单汇总';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        $objWriter->save('php://output');

    }

    /**下载订单详情
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     * @throws \PHPExcel_Writer_Exception
     */
    public function actionDownloadOrderDetail()
    {
        $invoice       = yii::$app->request->get('invoice');
        $rate          = yii::$app->request->get('rate');
        $excel         = new \PHPExcel();
        $model         = new Product();
        $product_list  = $model->get_invoice_product($invoice);
        $model         = new Income();
        $income_list   = $model->get_income_by_invoice($invoice);
        $model         = new Invoice_new();
        $detail        = $model->getDetailByInvoice($invoice);
        $invoice_model = new Invoice_new();
        $info          = $invoice_model->get_invoice_info_by_invoice($invoice);

        $excel->setActiveSheetIndex(0)
            ->setCellValue('A1', '客户名称:')
            ->setCellValue('B1', $detail['customer_name'])
            ->setCellValue('D1', '国家')
            ->setCellValue('E1', $info['country'])
            ->setCellValue('G1', 'INVOICE:')
            ->setCellValue('H1', $invoice)
            ->setCellValue('A3', '日期')
            ->setCellValue('B3', '编号')
            ->setCellValue('C3', '名称')
            ->setCellValue('D3', '采购单价')
            ->setCellValue('E3', '数量')
            ->setCellValue('F3', '额外费用')
            ->setCellValue('G3', '备注')
            ->setCellValue('H3', '金额')
            ->setCellValue('I3', 'Name')
            ->setCellValue('J3', 'Price')
            ->setCellValue('K3', 'Extra Fee')
            ->setCellValue('L3', 'Subtotal')
            ->setCellValue('M3', '利润')
            ->setCellValue('N3', '利润比');
        $num          = 3;
        $total_rmb    = 0;
        $total_dollar = 0;
        foreach ($product_list as $k => $v) {
            $num++;
            $dollar = $v['price_dollar'] * $v['number'] + $v['customer_extra_fee'];
            $total_dollar += $dollar;
            $rmb = $v['price_rmb'] * $v['number'] + $v['extra_fee'];
            $total_rmb += $rmb;

            $excel->setActiveSheetIndex(0)->setCellValue('A' . $num, $detail['date'])
                ->setCellValue('B' . $num, $k + 1)
                ->setCellValue('C' . $num, $v['product_name'])
                ->setCellValue('D' . $num, $v['price_rmb'])
                ->setCellValue('E' . $num, $v['number'])
                ->setCellValue('F' . $num, $v['extra_fee'])
                ->setCellValue('G' . $num, $v['remark'])
                ->setCellValue('H' . $num, $rmb)
                ->setCellValue('I' . $num, $v['product_name_en'])
                ->setCellValue('J' . $num, $v['price_dollar'])
                ->setCellValue('K' . $num, $v['customer_extra_fee'])
                ->setCellValue('L' . $num, $dollar)
                ->setCellValue('M' . $num, $dollar * $rate - $rmb)
                ->setCellValue('N' . $num, $dollar ? round(((($dollar * $rate - $rmb) / ($dollar * $rate)) * 100), 2) . '%' : 0);
        }
        $num += 2;
        $excel->setActiveSheetIndex(0)
            ->setCellValue('A' . $num, '收款详情')
            ->setCellValue('B' . $num, '出货详情')
            ->setCellValue('C' . $num, '总支出')
            ->setCellValue('D' . $num, '汇率')
            ->setCellValue('E' . $num, '总金额')
            ->setCellValue('F' . $num, '总收入')
            ->setCellValue('G' . $num, '差额')
            ->setCellValue('H' . $num, '利润')
            ->setCellValue('I' . $num, '利润比');
        $num++;
        $income      = "";
        $real_income = 0;
        $type        = [1 => 'paypal', 2 => 'T/T', 3 => '西联'];
        foreach ($income_list as $v) {
            $real_income += ($v['in_come'] + $v['total_fee'] + $v['fee']);
            $income .= $v['date'] . $type[$v['pay_from']] . '收款' . round(($v['in_come'] + $v['total_fee'] + $v['fee']), 2) . "\n";
        }
        $lirun = $real_income * $rate - $total_rmb;
        $excel->setActiveSheetIndex(0)
            ->setCellValue('A' . $num, $income)
            ->setCellValue('B' . $num, $detail['detail'])
            ->setCellValue('C' . $num, $total_rmb)
            ->setCellValue('D' . $num, $rate)
            ->setCellValue('E' . $num, '$' . $total_dollar)
            ->setCellValue('F' . $num, '$' . $real_income)
            ->setCellValue('G' . $num, round(($real_income - $total_dollar), 2))
            ->setCellValue('H' . $num, round(($real_income * $rate - $total_rmb), 2));
        $tmp = $lirun ? $lirun / ($real_income * $rate) : 0;
        $tmp = sprintf('%.2f', $tmp * 100);
        $excel->setActiveSheetIndex(0)
            ->setCellValue('I' . $num, $tmp . '%');
        $service = new OrderService();
        $detail  = $service->orderDetail($invoice);
        $num     = $num + 4;

        $objWriter = $excel->setActiveSheetIndex(0);
        foreach ($detail['bankList'] as $v) {
            $objWriter->mergeCells('A' . $num . ':F' . $num);
            $msg = $v['date'] . $v['product'] . '-转出' . $v['out_money'] . '转入' . $v['in_money'] . '-' . $v['name'];
            $objWriter->setCellValue('A' . $num, $msg);
            $num++;
        }

        foreach ($detail['expressList'] as $v) {
            foreach ($detail['expressType'] as $v2) {
                if ($v['express_id'] == $v2['id']) {
                    $msg = $v2['name'] . '运费--' . $v['fee'] . '- 转出';
                    $objWriter->mergeCells('A' . $num . ':F' . $num);
                    $objWriter->setCellValue('A' . $num, $msg);
                    $num++;
                }
            }
        }
        foreach ($detail['repoList'] as $v) {
            $msg = $v['date'] . '-仓库费' . $v['product'] . '- 转出:' . $v['pay_all'];
            $objWriter->mergeCells('A' . $num . ':F' . $num);
            $objWriter->setCellValue('A' . $num, $msg);
            $num++;
        }

        foreach ($detail['printList'] as $v) {
            $msg = $v['date'] . '印刷费' . $v['product'] . '-转出' . $v['fee'];
            $objWriter->mergeCells('A' . $num . ':F' . $num);
            $objWriter->setCellValue('A' . $num, $msg);
            $num++;
        }

        foreach ($detail['inlandList'] as $v) {
            $msg = $v['date'] . '内陆费' . $v['remark'] . '-转出' . $v['total_fee'];
            $objWriter->mergeCells('A' . $num . ':F' . $num);
            $objWriter->setCellValue('A' . $num, $msg);
            $num++;
        }
        $name = '订单详情';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        $objWriter->save('php://output');
    }

    public function actionFapiao()
    {
        $filePath = dirname(__DIR__) . '/statics/excel/兢赛开票资料.docx';
        yii::$app->response->sendFile($filePath);
    }

    /**
     *采购合同下载
     *
     */
    public function actionPurchase()
    {
        pclose(popen("php /mnt/hgfs/work/aaa.php &", 'r'));
        $excel = new \PHPExcel();
        $excel->setActiveSheetIndex(0)
            ->setCellValue('A1', '采购')
            ->setCellValue('B1', '业务员')
            ->setCellValue('C1', '采购编号')
            ->setCellValue('D1', '采购序号')
            ->setCellValue('E1', 'invoice编号')
            ->setCellValue('F1', '已付金额')
            ->setCellValue('G1', '签订日期')
            ->setCellValue('H1', '交货日期')
            ->setCellValue('I1', '采购商品')
            ->setCellValue('J1', '供应商')
            ->setCellValue('K1', '备注信息')
            ->setCellValue('L1', '总金额')
            ->setCellValue('M1', '定金');
        $model      = new Purchase();
        $list       = $model->all([], false, false, ['create_at' => SORT_DESC]);
        $staffModel = new Staff();
        $staffList  = $staffModel->get_all_saleman();
        foreach ($list as $v) {
            $row          = 2;
            $purchaseName = '';
            $sellName     = '';
            foreach ($staffList as $v2) {
                if ($v2['job_number'] == $v['job_number']) {
                    $purchaseName = $v2['name'];
                }
                if ($v2['job_number'] == $v['service']) {
                    $sellName = $v2['name'];
                }
            }
            $excel->setActiveSheetIndex(0)
                ->setCellValue('A' . $row, $purchaseName)
                ->setCellValue('B' . $row, $sellName)
                ->setCellValue('C' . $row, $v['purchase_num'])
                ->setCellValue('D' . $row, $v['s_num'])
                ->setCellValue('E' . $row, $v['invoice'])
                ->setCellValue('F' . $row, $v['aaa']);
        }
    }

    /**
     *导出客户信息excel
     *
     *
     */
    public function actionCustomer()
    {
        $where                  = [];
        $where['number']        = yii::$app->request->get('number');
        $where['email']         = yii::$app->request->get('email');
        $where['follow_man']    = yii::$app->request->get('follow_man');
        $where['property']      = yii::$app->request->get('property');
        $where['refer']         = yii::$app->request->get('refer');
        $where['customer_name'] = yii::$app->request->get('customerName');
        //员工列表
        $staffModel = new Staff();
        $staffList  = $staffModel->get_all_saleman();
        $model      = new Customer();
        $list       = $model->get_customer_list(false, false, $where);
        $referModel = new CustomerRefer();
        $referList  = $referModel->list();
        $excel      = new \PHPExcel();
        $excel->getActiveSheet(0)
            ->setCellValue('A1', '客户编号')
            ->setCellValue('B1', '客户名称')
            ->setCellValue('C1', '邮箱')
            ->setCellValue('D1', '邮箱2')
            ->setCellValue('E1', '跟进人')
            ->setCellValue('F1', '订单属性')
            ->setCellValue('G1', '下单次数')
            ->setCellValue('H1', '联系电话')
            ->setCellValue('I1', '客户来源')
            ->setCellValue('J1', '国家')
            ->setCellValue('K1', '客户备注')
            ->setCellValue('L1', 'skype')
            ->setCellValue('M1', 'whatsapp')
            ->setCellValue('N1', 'wechat');
        $property = [
            0 => '请选择',
            1 => '未下单',
            2 => '样品单',
            3 => '已下单',
        ];
        $row = 2;
        foreach ($list as $k => $v) {
            $refer = '';
            foreach ($referList as $v2) {
                if ($v2['id'] == $v['refer']) {
                    $refer = $v2['name'];
                    break;
                }
            }
            $staffName = '';
            $staff     = json_decode($v['follow_man'], true);
            if ($staff) {
                foreach ($staffList as $k2 => $v2) {
                    if (is_array($staff)) {
                        $num = $staff[0];
                    } else {
                        $num = $staff;
                    }
                    if ($v2['job_number'] == $num) {
                        $staffName = $v2['name'];
                    }
                }
            }

            $excel->getActiveSheet(0)
                ->setCellValue('A' . $row, $v['number'])
                ->setCellValue('B' . $row, $v['customer_name'])
                ->setCellValue('C' . $row, $v['email'])
                ->setCellValue('D' . $row, $v['email2'])
                ->setCellValue('E' . $row, $staffName)
                ->setCellValue('F' . $row, $property[$v['property']])
                ->setCellValue('G' . $row, $v['order_times'])
                ->setCellValue('H' . $row, $v['phone'])
                ->setCellValue('I' . $row, $refer)
                ->setCellValue('J' . $row, $v['country'])
                ->setCellValue('K' . $row, $v['customer_remark'])
                ->setCellValue('L' . $row, $v['skype'])
                ->setCellValue('M' . $row, $v['whatsapp'])
                ->setCellValue('N' . $row, $v['wechat']);

            $row++;
        }
        $name = '客户信息';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        $objWriter->save('php://output');

    }

    /**
     *装箱产品出库表
     *
     */
    public function actionContainerProduct()
    {
        $page = $pageSize = false;
        //1全部2未出货3出货4未排柜
        $status = 1;
        if ($status == 1) {
        } elseif ($status == 2) {
            $where['status'] = [1, 2];
        } elseif ($status == 3) {
            $where['status'] = 3;
        } elseif ($status == 4) {
            $where['container_num_empty'] = true;
        } elseif ($status == 5) {
            $where['container_num_empty'] = false;
        }
        //搜索优先级更高
        if (!empty($container_num) || !empty($number)) {
            $where['container_num'] = $container_num ? $container_num : $number;
        }

        $model = new ContainerProductModel();
        //有货柜编号
        if ((!empty($where['container_num'])) || (!empty($where['name']))) {
            $list  = $model->all($where, ($page - 1) * $pageSize, $pageSize, ['create_at' => SORT_DESC, 'id' => SORT_DESC]);
            $total = (int) $model->size($where);
        } else {
            $where['split_pid'] = 0;
            $list               = $model->all($where, ($page - 1) * $pageSize, $pageSize, ['create_at' => SORT_DESC, 'id' => SORT_DESC]);
            $pidArr             = array_column($list, 'id');
            $list2              = $model->all(['split_pid' => $pidArr]);
            foreach ($list as $k => $v) {
                foreach ($list2 as $k2 => $v2) {
                    if ($v['id'] == $v2['split_pid']) {
                        $list[$k]['children'][] = $v2;
                    }
                }
            }
            foreach ($list as $key => $value) {
                if (isset($value['children'])) {
                    $list[$key]['childsum'] = array_sum(array_column($value['children'], 'num'));
                } else {
                    $list[$key]['childsum'] = 0;
                }
            }
            if ($status == 4) {
                foreach ($list as $k => $v) {
                    if (isset($v['children'])) {
                        foreach ($v['children'] as $k2 => $v2) {
                            if ($v2['container_num'] != '') {
                                unset($list[$k]['children'][$k2]);
                            }
                        }
                    }
                }
            }
            $total = (int) $model->size($where);
        }
        //获取采购编号计算已付未付信息
        $purchaseNumArr = array_column($list, 'purchase_num');
        $bankModel      = new Bank_bill();
        $payList        = $bankModel->getPurchaseNumPay($purchaseNumArr, 'purchase_num,in_money,out_money');
        //获取采购编号订单总金额
        $model      = new Purchase();
        $needPay    = $model->all(['purchase_num' => $purchaseNumArr]);
        $needPayArr = [];
        foreach ($needPay as $key => $v) {
            $needPayArr[$v['purchase_num']] = $v['total_fee'];
        }
        $tmp = [];
        foreach ($payList as $v) {
            $m = $v['out_money'] - $v['in_money'];
            if (isset($tmp[$v['purchase_num']])) {
                $tmp[$v['purchase_num']] += $m;
            } else {
                $tmp[$v['purchase_num']] = $m;
            }
        }

        foreach ($list as $k => $v) {
            if (!isset($tmp[$v['purchase_num']])) {
                $tmp[$v['purchase_num']] = 0;
            }
            //订单已付金额
            $moneyAll            = $tmp[$v['purchase_num']];
            $notPay              = $needPayArr[$v['purchase_num']] - $moneyAll;
            $rate                = ($v['num'] * $v['price']) / $needPayArr[$v['purchase_num']];
            $list[$k]['havePay'] = round($moneyAll * $rate, 2);
            $list[$k]['notPay']  = round($notPay * $rate, 2);
        }
        // echo json_encode($list);exit;
        $containerModel = new ContainerModel();
        $containerList  = $containerModel->all();
        $excel          = new \PHPExcel();
        $excel->getActiveSheet(0)
            ->setCellValue('A1', 'id')
            ->setCellValue('B1', '日期')
            ->setCellValue('C1', '采购')
            ->setCellValue('D1', '采购编号')
            ->setCellValue('E1', '品名')
            ->setCellValue('F1', '已付定金')
            ->setCellValue('G1', '已付尾款')
            ->setCellValue('H1', '未付金额')
            ->setCellValue('I1', '数量')
            ->setCellValue('J1', '单价')
            ->setCellValue('K1', '货柜编号')
            ->setCellValue('L1', '状态')
            ->setCellValue('M1', '是否拆分');
        $row    = 1;
        foreach ($list as $key => $v) {
            $row++;
            $excel = $this->_setRow($excel, $row, $v, $containerList);
            if (!empty($v['children'])) {
                foreach ($v['children'] as $key => $value) {
                    $row++;
                    $excel = $this->_setRow($excel, $row, $value, $containerList, true);
                }
            }
        }

        $name = '未排柜产品';
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($excel, 'Excel5');
        $objWriter->save('php://output');
    }

    public function _setRow($excel, $row, $v, $containerList, $isChildren = false)
    {
        $status = [
            1 => '未入库',
            2 => '入库',
            3 => '出库',
        ];
        if (!$isChildren) {
            $total = $v['price'] * $v['num'];
            if ($total != 0) {
                if ($v['havePay'] / $total > 0.85) {
                    $excel->getActiveSheet(0)->setCellValue('G' . $row, $v['havePay']);
                } else {
                    $excel->getActiveSheet(0)->setCellValue('F' . $row, $v['havePay']);
                }
                $excel->getActiveSheet(0)->setCellValue('H' . $row, $total - $v['havePay']);

            }
        }

        $excel->getActiveSheet(0)
            ->setCellValue('A' . $row, $v['id'])
            ->setCellValue('B' . $row, date('Y-m-d', $v['create_at']))
            ->setCellValue('C' . $row, $v['staff'])
            ->setCellValue('D' . $row, $v['purchase_num'])
            ->setCellValue('E' . $row, $v['name'])
            ->setCellValue('I' . $row, $v['num'])
            ->setCellValue('J' . $row, $v['price'])
            ->setCellValue('L' . $row, $status[$v['status']]);
        if ($isChildren) {
            $excel->getActiveSheet(0)->setCellValue('M'.$row, '拆分项');
        }
        foreach ($containerList as $k => $v2) {
            if ($v2['id'] == $v['container_num']) {
                $tmp = $v2['number'];
                $excel->getActiveSheet(0)->setCellValue('K' . $row, $tmp);
            }
        }
        return $excel;
    }

}
